# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter

from pymysql import connect


class ScrapyDemoPipeline:
    def process_item(self, item, spider):
        print("ScrapyDemoPipeline->process_item 被调用")
        return item


class JDPipeline:

    # 写入文件
    # def open_spider(self, spider):
    #     print("写入操作被打开...")
    #     self.jd_write = open(r"E:\workSpace19\pythonL\jd_comment_Pipeline.txt", mode='w', encoding='utf-8')

    def open_spider(self, spider):
        self.con = connect(user='root', password='123456', host='192.168.116.90', database='spider', port=3306,
                           charset='utf8')
        self.cur = self.con.cursor()

    def process_item(self, item, spider):
        print("item['content']:", item['content'])
        sql = "INSERT INTO jd_comment (id,content,creationTime,score,productColor,productSize) values ('{}','{}','{}','{}','{}','{}')" \
            .format(
            item['id'][0],
            item['content'][0],
            item['creationTime'][0],
            item['score'][0],
            item['productColor'][0],
            item['productSize'][0]
        )
        print("====>", sql)
        res = self.cur.execute(sql)
        print("已插入{}条数据".format(res))
        self.con.commit()
        return item

    def close_spider(self, spider):
        print("Mysql连接已关闭...")
        self.con.close()

    # def close_spider(self, spider):
    #     print("写入操作被关闭...")
    #     self.jd_write.close()


# 需要在Settings中添加Pipeline配置
class DoubanPipeline():
    def open_spider(self, spider):
        self.con = connect(user='root', password='123456', host='192.168.116.90', database='spider', port=3306,
                           charset='utf8')
        self.cur = self.con.cursor()

    # item 为Spider中传过来的对象
    def process_item(self, item, spider):
        # sql中需要添加单引号
        sql = "INSERT INTO douban values('{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(
            # 用.属性方式会报错
            item['movie_name'],
            item['dy_name'],
            item['zy_name'],
            item['year'],
            item['country'],
            item['movie_type'],
            item['score'],
            item['people_num'],
            item['topic_desc']
        )
        print(sql)
        self.cur.execute(sql)
        self.con.commit()

    def close_spider(self, spider):
        print("Mysql连接已关闭...")
        self.con.close()